/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mantechhelpdesk.dao;

import mantechhelpdesk.entity.Complaints;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import mantechhelpdesk.entity.Users;

/**
 *
 * @author DinhThang
 */
public class ComplaintsDao {

    /**
     * createValueObject-method. This method is used when the Dao class needs to
     * create new value object instance. The reason why this method exists is
     * that sometimes the programmer may want to extend also the valueObject and
     * then this method can be overrided to return extended valueObject. NOTE:
     * If you extend the valueObject class, make sure to override the clone()
     * method in it!
     */
    public Complaints createValueObject() {
        return new Complaints();
    }

    /**
     * getObject-method. This will create and load valueObject contents from
     * database using given Primary-Key as identifier. This method is just a
     * convenience method for the real load-method which accepts the valueObject
     * as a parameter. Returned valueObject will be created using the
     * createValueObject() method.
     */
    public Complaints getObject(Connection conn, int ComplaintID) throws NotFoundException, SQLException {

        Complaints valueObject = createValueObject();
        valueObject.setComplaintID(ComplaintID);
        load(conn, valueObject);
        return valueObject;
    }

    public Complaints getComplaintsByUserId(Connection conn, int userID) throws NotFoundException, SQLException {

        Complaints valueObject = new Complaints();
        String sql = "SELECT TOP 1 * FROM Complaints WHERE (UserID = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, userID);

            singleQuery(conn, stmt, valueObject);

        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        return valueObject;
    }

    public Complaints getComplaintsByHasReplied(Connection conn, boolean hasReplied) throws NotFoundException, SQLException {

        Complaints valueObject = new Complaints();
        String sql = "SELECT TOP 1 * FROM Complaints WHERE (HasReplied = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setBoolean(1, hasReplied);

            singleQuery(conn, stmt, valueObject);

        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        return valueObject;
    }

    /**
     * load-method. This will load valueObject contents from database using
     * Primary-Key as identifier. Upper layer should use this so that
     * valueObject instance is created and only primary-key should be specified.
     * Then call this method to complete other persistent information. This
     * method will overwrite all other fields except primary-key and possible
     * runtime variables. If load can not find matching row, NotFoundException
     * will be thrown.
     *
     * @param conn This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * loaded. Primary-key field must be set for this to work properly.
     */
    public void load(Connection conn, Complaints valueObject) throws NotFoundException, SQLException {

        String sql = "SELECT * FROM Complaints WHERE (ComplaintID = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, valueObject.getComplaintID());

            singleQuery(conn, stmt, valueObject);

        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    /**
     * LoadAll-method. This will read all contents from database table and build
     * a List containing valueObjects. Please note, that this method will
     * consume huge amounts of resources if table has lot's of rows. This should
     * only be used when target tables have only small amounts of data.
     *
     * @param conn This method requires working database connection.
     */
    public List loadAll(Connection conn, int userId, boolean flag) throws SQLException {
        Users users = new Users();
        try {
            UsersDao usersDao = new UsersDao();
            users = usersDao.getObject(conn, userId);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        if (users.getUserID() > 0) {
            PreparedStatement prepareStatement = null;
            if (users.getRoleID() == 3) {
                String sql = "SELECT * FROM Complaints WHERE UserID=? AND HasReplied = ? ORDER BY ComplaintID ASC ";
                prepareStatement = conn.prepareStatement(sql);
                prepareStatement.setInt(1, userId);
                prepareStatement.setBoolean(2, flag);
            }
            if (users.getRoleID() == 2) {
                String sql = "SELECT * FROM Complaints WHERE DepartmentID NOT IN(?) AND HasReplied = ? ORDER BY ComplaintID ASC ";
                prepareStatement = conn.prepareStatement(sql);
                prepareStatement.setInt(1, users.getDepartmentID());
                prepareStatement.setBoolean(2, flag);
            }
            if (users.getRoleID() == 1) {
                String sql = "SELECT * FROM Complaints WHERE HasReplied = ? ORDER BY ComplaintID ASC ";
                prepareStatement = conn.prepareStatement(sql);
                prepareStatement.setBoolean(1, flag);
            }
            List searchResults = listQuery(conn, prepareStatement);
            return searchResults;
        }

        return null;
    }

    /**
     * create-method. This will create new row in database according to supplied
     * valueObject contents. Make sure that values for all NOT NULL columns are
     * correctly specified. Also, if this table does not use automatic
     * surrogate-keys the primary-key must be specified. After INSERT command
     * this method will read the generated primary-key back to valueObject if
     * automatic surrogate-keys were used.
     *
     * @param conn This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * created. If automatic surrogate-keys are not used the Primary-key field
     * must be set for this to work properly.
     */
    public synchronized void create(Connection conn, Complaints valueObject) throws SQLException {

        String sql = "";
        PreparedStatement stmt = null;
        ResultSet result = null;

        try {
            sql = "INSERT INTO Complaints (ThreadID, UserID, "
                    + "DepartmentID, Title, ComplaintContent, "
                    + "CreateTime, IsFinished, FinishedTime, "
                    + "HasReplied, UserRef, Priority) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            stmt = conn.prepareStatement(sql);

            //stmt.setInt(1, valueObject.getComplaintID());
            stmt.setInt(1, valueObject.getThreadID());
            stmt.setInt(2, valueObject.getUserID());
            stmt.setInt(3, valueObject.getDepartmentID());
            stmt.setString(4, valueObject.getTitle());
            stmt.setString(5, valueObject.getComplaintContent());
            stmt.setDate(6, new java.sql.Date(valueObject.getCreateTime().getTime()));
            stmt.setBoolean(7, valueObject.getIsFinished());
            stmt.setDate(8, new java.sql.Date(valueObject.getFinishedTime().getTime()));
            stmt.setBoolean(9, valueObject.getHasReplied());
            stmt.setInt(10, valueObject.getUserRef());
            stmt.setInt(11, valueObject.getPriority());

            int rowcount = databaseUpdate(conn, stmt);
            if (rowcount != 1) {
                //System.out.println("PrimaryKey Error when updating DB!");
                throw new SQLException("PrimaryKey Error when updating DB!");
            }

        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }


    }

    /**
     * save-method. This method will save the current state of valueObject to
     * database. Save can not be used to create new instances in database, so
     * upper layer must make sure that the primary-key is correctly specified.
     * Primary-key will indicate which instance is going to be updated in
     * database. If save can not find matching row, NotFoundException will be
     * thrown.
     *
     * @param conn This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * saved. Primary-key field must be set for this to work properly.
     */
    public void save(Connection conn, Complaints valueObject)
            throws NotFoundException, SQLException {

        String sql = "UPDATE Complaints SET ThreadID = ?, UserID = ?, DepartmentID = ?, "
                + "Title = ?, ComplaintContent = ?, CreateTime = ?, "
                + "IsFinished = ?, FinishedTime = ?, HasReplied = ?, "
                + "UserRef = ?, Priority = ? WHERE (ComplaintID = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, valueObject.getThreadID());
            stmt.setInt(2, valueObject.getUserID());
            stmt.setInt(3, valueObject.getDepartmentID());
            stmt.setString(4, valueObject.getTitle());
            stmt.setString(5, valueObject.getComplaintContent());
            stmt.setDate(6, new java.sql.Date(valueObject.getCreateTime().getTime()));
            stmt.setBoolean(7, valueObject.getIsFinished());
            stmt.setDate(8, new java.sql.Date(valueObject.getFinishedTime().getTime()));
            stmt.setBoolean(9, valueObject.getHasReplied());
            stmt.setInt(10, valueObject.getUserRef());
            stmt.setInt(11, valueObject.getPriority());

            stmt.setInt(12, valueObject.getComplaintID());

            int rowcount = databaseUpdate(conn, stmt);
            if (rowcount == 0) {
                //System.out.println("Object could not be saved! (PrimaryKey not found)");
                throw new NotFoundException("Object could not be saved! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    /**
     * delete-method. This method will remove the information from database as
     * identified by by primary-key in supplied valueObject. Once valueObject
     * has been deleted it can not be restored by calling save. Restoring can
     * only be done using create method but if database is using automatic
     * surrogate-keys, the resulting object will have different primary-key than
     * what it was in the deleted object. If delete can not find matching row,
     * NotFoundException will be thrown.
     *
     * @param conn This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * deleted. Primary-key field must be set for this to work properly.
     */
    public void delete(Connection conn, Complaints valueObject)
            throws NotFoundException, SQLException {

        String sql = "DELETE FROM Complaints WHERE (ComplaintID = ? ) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, valueObject.getComplaintID());

            int rowcount = databaseUpdate(conn, stmt);
            if (rowcount == 0) {
                //System.out.println("Object could not be deleted (PrimaryKey not found)");
                throw new NotFoundException("Object could not be deleted! (PrimaryKey not found)");
            }
            if (rowcount > 1) {
                //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    /**
     * deleteAll-method. This method will remove all information from the table
     * that matches this Dao and ValueObject couple. This should be the most
     * efficient way to clear table. Once deleteAll has been called, no
     * valueObject that has been created before can be restored by calling save.
     * Restoring can only be done using create method but if database is using
     * automatic surrogate-keys, the resulting object will have different
     * primary-key than what it was in the deleted object. (Note, the
     * implementation of this method should be different with different DB
     * backends.)
     *
     * @param conn This method requires working database connection.
     */
    public void deleteAll(Connection conn) throws SQLException {

        String sql = "DELETE FROM Complaints";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            int rowcount = databaseUpdate(conn, stmt);
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    /**
     * coutAll-method. This method will return the number of all rows from table
     * that matches this Dao. The implementation will simply execute "select
     * count(primarykey) from table". If table is empty, the return value is 0.
     * This method should be used before calling loadAll, to make sure table has
     * not too many rows.
     *
     * @param conn This method requires working database connection.
     */
    public int countAll(Connection conn) throws SQLException {

        String sql = "SELECT count(*) FROM Complaints";
        PreparedStatement stmt = null;
        ResultSet result = null;
        int allRows = 0;

        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();

            if (result.next()) {
                allRows = result.getInt(1);
            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
        return allRows;
    }

    /**
     * searchMatching-Method. This method provides searching capability to get
     * matching valueObjects from database. It works by searching all objects
     * that match permanent instance variables of given object. Upper layer
     * should use this by setting some parameters in valueObject and then call
     * searchMatching. The result will be 0-N objects in a List, all matching
     * those criteria you specified. Those instance-variables that have NULL
     * values are excluded in search-criteria.
     *
     * @param conn This method requires working database connection.
     * @param valueObject This parameter contains the class instance where
     * search will be based. Primary-key field should not be set.
     */
    public List searchMatching(Connection conn, Complaints valueObject) throws SQLException {

        List searchResults;

        boolean first = true;
        StringBuffer sql = new StringBuffer("SELECT * FROM Complaints WHERE 1=1 ");

        if (valueObject.getComplaintID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND ComplaintID = ").append(valueObject.getComplaintID()).append(" ");
        }

        if (valueObject.getThreadID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND ThreadID = ").append(valueObject.getThreadID()).append(" ");
        }

        if (valueObject.getUserID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND UserID = ").append(valueObject.getUserID()).append(" ");
        }

        if (valueObject.getDepartmentID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND DepartmentID = ").append(valueObject.getDepartmentID()).append(" ");
        }

        if (valueObject.getTitle() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND Title LIKE '").append(valueObject.getTitle()).append("%' ");
        }

        if (valueObject.getComplaintContent() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND ComplaintContent LIKE '").append(valueObject.getComplaintContent()).append("%' ");
        }

        if (valueObject.getCreateTime() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND CreateTime = '").append(valueObject.getCreateTime()).append("' ");
        }

        if (valueObject.getFinishedTime() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND FinishedTime = '").append(valueObject.getFinishedTime()).append("' ");
        }


        sql.append("ORDER BY ComplaintID ASC ");

        // Prevent accidential full table results.
        // Use loadAll if all rows must be returned.
        if (first) {
            searchResults = new ArrayList();
        } else {
            searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));
        }

        return searchResults;
    }

    /**
     * databaseUpdate-method. This method is a helper method for internal use.
     * It will execute all database handling that will change the information in
     * tables. SELECT queries will not be executed here however. The return
     * value indicates how many rows were affected. This method will also make
     * sure that if cache is used, it will reset when data changes.
     *
     * @param conn This method requires working database connection.
     * @param stmt This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

        int result = stmt.executeUpdate();

        return result;
    }

    /**
     * databaseQuery-method. This method is a helper method for internal use. It
     * will execute all database queries that will return only one row. The
     * resultset will be converted to valueObject. If no rows were found,
     * NotFoundException will be thrown.
     *
     * @param conn This method requires working database connection.
     * @param stmt This parameter contains the SQL statement to be excuted.
     * @param valueObject Class-instance where resulting data will be stored.
     */
    protected void singleQuery(Connection conn, PreparedStatement stmt, Complaints valueObject)
            throws NotFoundException, SQLException {

        ResultSet result = null;

        try {
            result = stmt.executeQuery();

            if (result.next()) {

                valueObject.setComplaintID(result.getInt("ComplaintID"));
                valueObject.setThreadID(result.getInt("ThreadID"));
                valueObject.setUserID(result.getInt("UserID"));
                valueObject.setDepartmentID(result.getInt("DepartmentID"));
                valueObject.setTitle(result.getString("Title"));
                valueObject.setComplaintContent(result.getString("ComplaintContent"));
                valueObject.setCreateTime(new java.util.Date(result.getDate("CreateTime").getTime()));
                valueObject.setIsFinished(result.getBoolean("IsFinished"));
                valueObject.setFinishedTime(new java.util.Date(result.getDate("FinishedTime").getTime()));
                valueObject.setHasReplied(result.getBoolean("HasReplied"));
                valueObject.setUserRef(result.getInt("UserRef"));
                valueObject.setPriority(result.getInt("Priority"));

            } else {
                //System.out.println("Complaints Object Not Found!");
                throw new NotFoundException("Complaints Object Not Found!");
            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
    }

    /**
     * databaseQuery-method. This method is a helper method for internal use. It
     * will execute all database queries that will return multiple rows. The
     * resultset will be converted to the List of valueObjects. If no rows were
     * found, an empty List will be returned.
     *
     * @param conn This method requires working database connection.
     * @param stmt This parameter contains the SQL statement to be excuted.
     */
    protected List listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

        ArrayList searchResults = new ArrayList();
        ResultSet result = null;

        try {
            result = stmt.executeQuery();

            while (result.next()) {
                Complaints temp = createValueObject();

                temp.setComplaintID(result.getInt("ComplaintID"));
                temp.setThreadID(result.getInt("ThreadID"));
                temp.setUserID(result.getInt("UserID"));
                temp.setDepartmentID(result.getInt("DepartmentID"));
                temp.setTitle(result.getString("Title"));
                temp.setComplaintContent(result.getString("ComplaintContent"));
                temp.setCreateTime(new java.util.Date(result.getDate("CreateTime").getTime()));
                temp.setIsFinished(result.getBoolean("IsFinished"));
                temp.setFinishedTime(new java.util.Date(result.getDate("FinishedTime").getTime()));
                temp.setHasReplied(result.getBoolean("HasReplied"));
                temp.setUserRef(result.getInt("UserRef"));
                temp.setPriority(result.getInt("Priority"));

                searchResults.add(temp);
            }

        } finally {
            if (result != null) {
                result.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }

        return (List) searchResults;
    }
}
